﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QL_DTTN.DAL;

namespace QL_DTTN.DTO.DTO
{
    public partial class KetQuaThi
    {
        public DataTable DataKetQuaThi(string soBaoDanh)
        {
            string sql = "select  M2.TenMon,isNUll(K.KetQua,0) as KetQua  ";
            sql += " from ThiSinh T left join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao ";
            sql += " inner join MonThi M on M.MaMonThi=C.MaMonThi and M.maKyThi=T.maKyThi";
            sql += " left join Mon M2 on M2.MaMon=M.MaMon ";
            sql += " left join KetQuaThi K on K.MaMon=M.MaMon and K.SoBaoDanh=T.SoBaoDanh ";
            sql += " where T.SoBaoDanh = @pSoBaoDanh ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pSoBaoDanh", soBaoDanh));
            return dp.ExDataTable(sql, true);
        }
        public double TongDiemThi(string soBaoDanh)
        {
            string sql = " select  Sum(isNUll(K.KetQua,0)) ";
            sql += " from ThiSinh T left join ChiTietMonThi C on C.MaHeDaoTao=T.MaHeDaoTao ";
            sql += " inner join MonThi M on M.MaMonThi=C.MaMonThi and M.maKyThi=T.maKyThi";
            sql += " left join Mon M2 on M2.MaMon=M.MaMon ";
            sql += " left join KetQuaThi K on K.MaMon=M.MaMon and K.SoBaoDanh=T.SoBaoDanh ";
            sql += " where T.SoBaoDanh = @pSoBaoDanh ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pSoBaoDanh", soBaoDanh));
            object kq = 0;
            dp.RunReturnSQL(sql, ref kq, true);
            return Convert.ToDouble(kq);
        }

      
    }
}
